﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BusinessLogic;
using System.Data.SqlClient;
using System.Data;
using System.Web.Security;

namespace DataAccessLogic
{
    public static class AddressDataAccess
    {
        private static Address BindRowToAddress(DataRow row)
        {
            Address a = new Address();
            a.ID = DataAccess.CheckForGuidColumn(row, "ID");
            a.AddressBlock = DataAccess.CheckForStringColumn(row, "ADDRESSBLOCK");
            a.City = DataAccess.CheckForStringColumn(row, "CITY");
            a.PostCode = DataAccess.CheckForStringColumn(row, "POSTCODE");            
            a.IsPrimary = DataAccess.CheckForBooleanColumn(row, "ISPRIMARY");            
            a.IsActive = DataAccess.CheckForBooleanColumn(row, "ISACTIVE");
            a.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
            a.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
            a.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
            a.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");

            State s = new State();
            s.ID = DataAccess.CheckForGuidColumn(row, "STATEID");
            s.Description = DataAccess.CheckForStringColumn(row, "STATE");
            s.Abbreviation = DataAccess.CheckForStringColumn(row, "STATEABBREVIATION");
            a.State = s;

            Country c = new Country();
            c.ID = DataAccess.CheckForGuidColumn(row, "COUNTRYID");
            c.Description = DataAccess.CheckForStringColumn(row, "COUNTRY");
            c.Abbreviation = DataAccess.CheckForStringColumn(row, "COUNTRYABBREVIATION");
            a.Country = c;

            AddressType at = new AddressType();
            at.ID = DataAccess.CheckForGuidColumn(row, "ADDRESSTYPECODEID");
            at.Description = DataAccess.CheckForStringColumn(row, "ADDRESSTYPE");
            a.AddressType = at;

            return a;
        }

        public static Guid Create(Address address, Guid constituentID)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            string outputParameterName = "@ID";

            if ((address != null) && (constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@ADDRESSTYPECODEID", address.AddressType.ID));
                parms.Add(new SqlParameter("@ADDRESSBLOCK", address.AddressBlock));
                parms.Add(new SqlParameter("@CITY", address.City));
                parms.Add(new SqlParameter("@STATEID", address.State.ID));
                parms.Add(new SqlParameter("@POSTCODE", address.PostCode));
                parms.Add(new SqlParameter("@COUNTRYID", address.Country.ID));
                parms.Add(new SqlParameter("@ISPRIMARY", address.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_ADDRESS_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static Guid CreateForConversion(Address address, Guid constituentID)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid("c9014ff9-ee89-4192-aa6b-b0c2130557ba");

            string outputParameterName = "@ID";

            if ((address != null) && (constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@ADDRESSTYPECODEID", address.AddressType.ID));
                parms.Add(new SqlParameter("@ADDRESSBLOCK", address.AddressBlock));
                parms.Add(new SqlParameter("@CITY", address.City));
                parms.Add(new SqlParameter("@STATEABBREVIATION", (address.State != null ? address.State.Abbreviation : string.Empty)));
                parms.Add(new SqlParameter("@POSTCODE", address.PostCode));
                parms.Add(new SqlParameter("@COUNTRYID", address.Country.ID));
                parms.Add(new SqlParameter("@ISPRIMARY", address.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_ADDRESS_CREATEFORCONVERSION, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid id)
        {
            if (id != Guid.Empty)
            {
                DataAccess.Delete(StoredProcedures.USP_ADDRESS_DELETE, id);
            }
        }

        public static void DeleteByConstituentID(Guid constituentID)
        {
            if(constituentID != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DataAccess.Delete(StoredProcedures.USP_ADDRESS_DELETEBYCONSTITUENTID, parms);
            }
        }

        private static List<Address> Get(string storedProcedure, Guid id, string parameterName)
        {
            List<Address> addresses = null;

            if(id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter(parameterName, id));

                DataTable dt = DataAccess.Get(storedProcedure, parms);

                if((dt != null) && (dt.Rows.Count > 0))
                {
                    addresses = new List<Address>();

                    foreach (DataRow row in dt.Rows)
                    {
                        addresses.Add(BindRowToAddress(row));
                    }
                }
            }

            return addresses;
        }

        public static List<Address> GetActiveByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_ADDRESS_GETACTIVEBYCONSTITUENTID, constituentID, "@CONSTITUENTID");
        }

        public static List<Address> GetAllByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_ADDRESS_GETALLBYCONSTITUENTID, constituentID, "@CONSTITUENTID");
        }

        public static List<Address> GetByAddressID(Guid addressID)
        {
            return Get(StoredProcedures.USP_ADDRESS_GETBYADDRESSID, addressID, "@ADDRESSID");
        }

        public static Address GetPrimaryByConstituentID(Guid constituentID)
        {
            Address address = null;
            List<Address> addresses = Get(StoredProcedures.USP_ADDRESS_GETPRIMARYBYCONSTITUENTID, constituentID, "@CONSTITUENTID");

            if((addresses != null) && (addresses.Count > 0))
            {
                address = addresses[0];
            }

            return address;
        }

        public static void Update(Address address, Guid constituentID)
        {            
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());            

            if ((address != null) && (constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", address.ID));
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@ADDRESSTYPECODEID", address.AddressType.ID));
                parms.Add(new SqlParameter("@ADDRESSBLOCK", address.AddressBlock));
                parms.Add(new SqlParameter("@CITY", address.City));
                parms.Add(new SqlParameter("@STATEID", address.State.ID));
                parms.Add(new SqlParameter("@POSTCODE", address.PostCode));
                parms.Add(new SqlParameter("@COUNTRYID", address.Country.ID));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                DataAccess.Update(StoredProcedures.USP_ADDRESS_UPDATE, parms);
            }
        }
    }
}